package com.ankicoo.office.controller;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.format.datetime.DateFormatter;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.support.StandardMultipartHttpServletRequest;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.PrintWriter;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("/excel/parse")
public class ExcelParseController {
    @GetMapping("/toSql")
    public void toSql(StandardMultipartHttpServletRequest request, HttpServletResponse response)throws Exception{
        if (request.getFileMap().isEmpty()) {
            PrintWriter out;
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/json; charset=utf-8");
            out = response.getWriter();
            ObjectMapper mapper = new ObjectMapper();
            out.write(mapper.writeValueAsString("请上传正确格式的文件"));
        }else{
            for (MultipartFile multiFile : request.getFileMap().values()) {
                InputStream ins;
                if (!multiFile.isEmpty()&&multiFile.getOriginalFilename()!=null) {
                    ins = multiFile.getInputStream();
                }else{
                    throw new RuntimeException("文件内容为空");
                }
                Workbook workbook = new XSSFWorkbook(ins);
                Sheet sheet = workbook.getSheetAt(0);
                int lastRowNum = sheet.getLastRowNum();
                List<String> sql = new ArrayList<>();

                String sqlCon = "";

                for (int i = 1; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    String insert = sqlProcessDistrict(row);
                    sqlCon = sqlCon + insert;

                }
                response.addHeader("Content-Disposition", "attachment;filename="
                        + new String("Insert.txt".getBytes(StandardCharsets.UTF_8),"ISO8859-1"));
//                response.addHeader("content-type","application/json;charset=utf-8");
                response.setCharacterEncoding("utf-8");
                response.reset();
                FileOutputStream outputStream = new FileOutputStream("D:/insert.txt");
                outputStream.write(sqlCon.getBytes(StandardCharsets.UTF_8));
                outputStream.flush();
                outputStream.close();
            }
        }

    }


    private String sqlProcessDistrict(Row row) {
        Iterator<Cell> cellIterator = row.cellIterator();
        int index = 0;
        Map<String,Object> paramMap = new HashMap<>();
        String[] column = { "SUBJECT", "NAME","STATUS","STASH","CREATER","TIME","SOURCE" };

        while(cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (index == column.length){
                break;
            }
            setCellValue(cell,paramMap,column,index);
            index++;
        }
        String columnNames = String.join(",", column);
        String valueString = "";
        for (int i = 0; i < column.length-1; i++) {
            Object value = paramMap.get(column[i]);
            if ("null".equals(value)|| Objects.isNull(value)) {
                valueString = valueString +"NULL ,";

            }else {
                valueString = valueString +"'"+ value +"'" +" ,";
            }

        }
        valueString = valueString.substring(0,valueString.length()-2);

        String insert = "INSERT INTO FOWK_DICT_ITEM " +
                "( "+columnNames+") " +
                "VALUES(" +valueString+
                ");";
        System.out.println(insert);
        return insert;
    }

    private void setCellValue(Cell cell, Map<String,Object> param,String[] column, Integer index){
        switch (cell.getCellType()) {
            case 1:
                param.put(column[index],cell.getStringCellValue());break;
            case 0:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat format = new SimpleDateFormat("yyyy年-MM月-dd日");
                    Date date = cell.getDateCellValue();
                    param.put(column[index],format.format(date));
                } else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式
                    param.put(column[index], DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    param.put(column[index],cell.getNumericCellValue());
                }
                break;
            case 4:
                param.put(column[index],cell.getBooleanCellValue());break;
            default:break;
        }
    }
}
